package text.jdbc;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

/*
作业3：使用JDBC删除emp表中员工薪资少于平均薪资的员工数据
 */
public class JdbcText03 {
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        Properties properties = new Properties();
        properties.load(new BufferedReader(new FileReader("first_git/src/main/resources/Jdbc.properties")));
        Class.forName(properties.getProperty("Driver"));
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password =properties.getProperty("password");
        String sql1 = "delete from emp where sal<(select * from (select avg(sal) from emp) as a)";
        String sql2 = "select * from emp";
        Connection con = DriverManager.getConnection(url,user,password);
        Statement statement = con.createStatement();
        int rows = statement.executeUpdate(sql1);
        if(rows>0){
            System.out.println("删除成功，删除后的查询结果为:");
            ResultSet resultSet = statement.executeQuery(sql2);
            while (resultSet.next()){
                String ename = resultSet.getString("ename");
                String sal = resultSet.getString("sal");
                System.out.println(String.format("%s\t%s",ename,sal));
            }
        }else {
            System.out.println("没有符合条件的语句");
        }

        statement.close();
        con.close();


    }
}
